﻿using Microsoft.Extensions.Configuration;
using Sql_Windows_Sp_Tools.Common;
using Sql_Windows_Sp_Tools.Models;
using Sql_Windows_Sp_Tools.Repository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Sql_Windows_Sp_Tools
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
            Load += MainForm_Load;
        }

        #region 私有方法
        private void Txt_Error(string txt, RichTextBox richText)
        {
            //Log4NetHelper.WriteLog(txt, Log4NetHelper.Log4NetLevel.Error);

            RichTextBoxConsoleHelper.OutMsg(richText, txt, Color.Red);
        }
        private void Txt_Info(string txt, RichTextBox richText)
        {
            //Log4NetHelper.WriteLog(txt, Log4NetHelper.Log4NetLevel.Info);

            RichTextBoxConsoleHelper.OutMsg(richText, txt, Color.Green);
        }
        private void Txt_Warn(string txt, RichTextBox richText)
        {
            //Log4NetHelper.WriteLog(txt, Log4NetHelper.Log4NetLevel.Warn);

            RichTextBoxConsoleHelper.OutMsg(richText, txt, Color.Yellow);
        }
        private string GetFileName(string name)
        {
            return name.Replace("dbo.", "").Replace(".sql", "");
        }
        private Dictionary<string, string> GetSpFileList(string path)
        {
            var dic = new Dictionary<string, string>();
            GetSpFileList(path, "*.sql", ref dic);
            return dic;
        }
        private void GetSpFileList(string path, string pattern, ref Dictionary<string, string> dic)
        {
            DirectoryInfo dir = new DirectoryInfo(path);
            foreach (var item in dir.GetFiles(pattern))
            {
                dic.Add(GetFileName(item.Name), item.FullName);
            }
            foreach (var item in dir.GetDirectories())
            {
                GetSpFileList(item.FullName, pattern, ref dic);
            }
        }

        private List<TableSpModel> GetTableFileList(string path)
        {
            var list = new List<TableSpModel>();
            //dic.Add("Add_AreaEmp", "D:\\tonglei\\code\\MIDInfra_EMS_WebAPI\\EMSDatabase\\存储过程脚本\\人员维护\\dbo.Add_AreaEmp.sql");
            GetTableFileList(path, "*.sql", ref list);

            return list;
        }

        /// <summary>
        /// 获取文件夹下所有文件
        /// </summary>
        /// <param name="directory">文件夹路径</param>
        /// <param name="pattern">文件类型</param>
        /// <param name="list">集合</param>
        private void GetTableFileList(string directory, string pattern, ref List<TableSpModel> list)
        {
            DirectoryInfo directoryInfo = new DirectoryInfo(directory);
            foreach (FileInfo info in directoryInfo.GetFiles(pattern))
            {
                var version = "0";
                try
                {
                    //文件格式(1)dbo.test-v11.sql
                    //        (2)dbo.v11-test.sql
                    var vdata = info.Name.Replace("dbo.", "").Replace(".sql", "").Split('-');
                    if (Verify.IsVersion(vdata[0]))
                    {
                        version = vdata[0].Substring(1);
                    }
                    else
                    {
                        version = info.Name.Replace("dbo.", "").Replace(".sql", "").Split('-')[1].Substring(1);
                    }
                }
                catch (Exception e)
                {
                    Txt_Error($"数据库表更新名称异常：{info.Name}|例子：dbo.v11-test.sql", this.richTextBox2);
                }
                list.Add(new TableSpModel()
                {
                    version = Convert.ToInt32(version),
                    name = info.Name.Replace("dbo.", "").Replace(".sql", ""),
                    str_path = info.FullName
                });
            }
            foreach (DirectoryInfo info in directoryInfo.GetDirectories())
            {
                GetTableFileList(info.FullName, pattern, ref list);
            }
        }

        private void PaintTreeView(TreeView treeView, string fullPath, string pattern)
        {
            try
            {
                treeView.Nodes.Clear(); //清空TreeView

                DirectoryInfo dirs = new DirectoryInfo(fullPath); //获得程序所在路径的目录对象
                DirectoryInfo[] dir = dirs.GetDirectories();//获得目录下文件夹对象
                FileInfo[] file = dirs.GetFiles(pattern);//获得目录下文件对象
                int dircount = dir.Count();//获得文件夹对象数量
                int filecount = file.Count();//获得文件对象数量

                //循环文件夹
                for (int i = 0; i < dircount; i++)
                {
                    treeView.Nodes.Add(dir[i].Name);
                    string pathNode = fullPath + "\\" + dir[i].Name;
                    GetMultiNode(treeView.Nodes[i], pathNode, pattern);
                }

                //循环文件
                for (int j = 0; j < filecount; j++)
                {
                    treeView.Nodes.Add(file[j].Name);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private bool GetMultiNode(TreeNode treeNode, string path, string pattern)
        {
            if (Directory.Exists(path) == false)
            { return false; }

            DirectoryInfo dirs = new DirectoryInfo(path); //获得程序所在路径的目录对象
            DirectoryInfo[] dir = dirs.GetDirectories();//获得目录下文件夹对象
            FileInfo[] file = dirs.GetFiles(pattern);//获得目录下文件对象
            int dircount = dir.Count();//获得文件夹对象数量
            int filecount = file.Count();//获得文件对象数量
            int sumcount = dircount + filecount;

            if (sumcount == 0)
            { return false; }

            //循环文件夹
            for (int j = 0; j < dircount; j++)
            {
                treeNode.Nodes.Add(dir[j].Name);
                string pathNodeB = path + "\\" + dir[j].Name;
                GetMultiNode(treeNode.Nodes[j], pathNodeB, pattern);
            }

            //循环文件
            for (int j = 0; j < filecount; j++)
            {
                treeNode.Nodes.Add(file[j].Name);
            }
            return true;
        }
        /// <summary>
        /// 获取复选框已经选中的node的显示文字
        /// </summary>
        /// <param name="treeView">控件</param>
        /// <returns></returns>
        public string GetAllCheckedNodesName(TreeView treeView)
        {
            string str = string.Empty;
            TreeNodeCollection tnc = treeView.Nodes;
            foreach (TreeNode node in tnc)
            {
                str += GetAllCheckedNodesName(node);
            }
            return str.Trim(',');
        }

        /// <summary>
        /// 获取复选框已经选中的node的显示文字
        /// </summary>
        /// <param name="treeNode">节点</param>
        /// <returns></returns>
        public string GetAllCheckedNodesName(TreeNode treeNode)
        {
            string str = string.Empty;
            if (treeNode.Checked)
            {
                str = str + "," + treeNode.Text;
                if (treeNode.Nodes.Count > 0)
                {
                    TreeNodeCollection trc = treeNode.Nodes;
                    foreach (TreeNode node in trc)
                    {
                        str += GetAllCheckedNodesName(node);
                    }
                }
            }
            return str;
        }
        #endregion

        private void MainForm_Load(object sender, EventArgs e)
        {
            if (AppSettingConfig.dbconfigs.Count() <= 0)
            {
                MessageBox.Show("基地数据库配置有误，请检查appsettings.json文件");
            }
            this.tree_factory.Nodes.Clear();
            foreach (var item in AppSettingConfig.dbconfigs)
            {
                this.tree_factory.Nodes.Add(item.db_name);
            }
        }
        private void btn_factory_reset_Click(object sender, EventArgs e)
        {
            var builder = new ConfigurationBuilder()
                   .SetBasePath(Directory.GetCurrentDirectory())
                   .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
                   .Build();
            AppSettingConfig.dbconfigs = builder.GetSection("dbconfigs").Get<List<AppSettingConfig.DbConfig>>();
            this.tree_factory.Nodes.Clear();
            foreach (var item in AppSettingConfig.dbconfigs)
            {
                this.tree_factory.Nodes.Add(item.db_name);
            }
        }

        private void btn_select_factory_all_Click(object sender, EventArgs e)
        {
            var nodes = this.tree_factory.Nodes;
            foreach (TreeNode item in nodes)
            {
                item.Checked = true;
            }
        }
        private void btn_select_factory_clear_Click(object sender, EventArgs e)
        {
            var nodes = this.tree_factory.Nodes;
            foreach (TreeNode item in nodes)
            {
                item.Checked = false;
            }
        }

        #region sp
        private void btn_select_sp_path_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog dialog = new FolderBrowserDialog();
            dialog.Description = "请选择文件路径";
            if (dialog.ShowDialog() == DialogResult.OK)
            {
                this.txt_select_path.Text = dialog.SelectedPath;
                PaintTreeView(this.tree_sp, dialog.SelectedPath, "*.sql");
            }
        }

        private void btn_sp_exec_Click(object sender, EventArgs e)
        {
            var tree_factory_select_list = new List<string>();
            var ischeck = true;
            var txt_path = this.txt_select_path.Text;
            var tree_sp_select_list = new List<string>();

            tree_factory_select_list = GetAllCheckedNodesName(this.tree_factory).Split(',').Where(i => i != "").ToList();
            if (tree_factory_select_list.Count() <= 0)
            {
                ischeck = false;
                Txt_Error($"请选择要操作的基地信息！", this.richTextBox1);
            }
            if (string.IsNullOrEmpty(txt_path))
            {
                ischeck = false;
                Txt_Error($"请选择要操作的存储过程文件夹地址！", this.richTextBox1);
            }
            tree_sp_select_list = GetAllCheckedNodesName(this.tree_sp).Split(',').Where(i => i != "").ToList();
            if (tree_sp_select_list.Count() <= 0 && !string.IsNullOrEmpty(txt_path))
            {
                var all_tree_sp = GetSpFileList(txt_path);
                tree_sp_select_list = all_tree_sp.Select(i => i.Key).ToList();
            }
            else
            {
                tree_sp_select_list = tree_sp_select_list
                    .Where(i => i.LastIndexOf('.') > 0)
                    .Select(i => GetFileName(i))
                    .ToList();
            }

            if (ischeck)
            {
                Txt_Info($"执行的存储过程文件数量：{tree_sp_select_list.Count()}", this.richTextBox1);
                var dic_data = GetSpFileList(txt_path);
                foreach (var factory_item in tree_factory_select_list)
                {
                    Txt_Warn($"开始执行基地：{factory_item}", this.richTextBox1);
                    var dbpath = AppSettingConfig.dbconfigs.Where(i => i.db_name == factory_item).FirstOrDefault();
                    int indexnum = 0;
                    foreach (var dic_item in tree_sp_select_list)
                    {
                        var data = dic_data.Where(i => i.Key == dic_item).FirstOrDefault();
                        //Task.Run(() =>
                        //{
                        try
                        {
                            if (!string.IsNullOrEmpty(data.Key))
                            {
                                string[] strsql = File.ReadAllLines(data.Value, Encoding.GetEncoding("GB2312"));
                                var sb = new StringBuilder();
                                foreach (var sqlitem in strsql)
                                {
                                    if (sqlitem != "GO" && sqlitem != "go")
                                    {
                                        sb.Append($"{sqlitem}\n");
                                    }
                                }
                                var procedureRep = new ProcedureRep(dbpath.db_connection);
                                var spdata = procedureRep.IsExists(data.Key);
                                if (spdata == 1)
                                {
                                    procedureRep.Create(sb.ToString());
                                    Txt_Info($"删除存储过程-{data.Key}-后添加新存储过程", this.richTextBox1);
                                }
                                else
                                {
                                    procedureRep.Create(sb.ToString());
                                    Txt_Warn($"直接添加存储过程-{data.Key}", this.richTextBox1);
                                }
                            }
                            else
                            {
                                Txt_Warn($"存储过程不存在：{data.Key}，地址:{data.Value}", this.richTextBox1);
                            }

                        }
                        catch (Exception ex)
                        {
                            Txt_Error($"*****异常存储过程名称：{data.Key}，执行失败：{ex.Message}|{ex.StackTrace}|{ex.InnerException}", this.richTextBox1);
                        }
                        indexnum = indexnum + 1;
                        if (tree_sp_select_list.Count() <= indexnum)
                        {
                            Txt_Warn($"结束执行基地：{factory_item}", this.richTextBox1);
                        }
                        //});
                    }
                }
            }
        }

        private void tree_sp_AfterCheck(object sender, TreeViewEventArgs e)
        {
            var node = e.Node;

            if (node.Parent == null)
            {

                foreach (TreeNode childNode in node.Nodes)
                {
                    childNode.Checked = node.Checked;
                }
            }
        }

        private void btn_sp_clear_log_Click(object sender, EventArgs e)
        {
            this.richTextBox1.Text = "";
        }
        #endregion

        #region table
        private void btn_select_table_path_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog dialog = new FolderBrowserDialog();
            dialog.Description = "请选择文件路径";
            if (dialog.ShowDialog() == DialogResult.OK)
            {
                this.txt_table_select_path.Text = dialog.SelectedPath;
                PaintTreeView(this.tree_table, dialog.SelectedPath, "*.sql");
            }
        }
        private void btn_table_exec_Click(object sender, EventArgs e)
        {
            var tree_factory_select_list = new List<string>();
            var ischeck = true;
            var txt_path = this.txt_table_select_path.Text;
            var tree_table_select_list = new List<string>();

            tree_factory_select_list = GetAllCheckedNodesName(this.tree_factory).Split(',').Where(i => i != "").ToList();
            if (tree_factory_select_list.Count() <= 0)
            {
                ischeck = false;
                Txt_Error($"请选择要操作的基地信息！", this.richTextBox2);
            }

            if (string.IsNullOrEmpty(txt_path))
            {
                ischeck = false;
                Txt_Error($"请选择要操作的数据库表文件夹地址！", this.richTextBox2);
            }

            tree_table_select_list = GetAllCheckedNodesName(this.tree_table).Split(',').Where(i => i != "").ToList();
            if (tree_table_select_list.Count() <= 0 && !string.IsNullOrEmpty(txt_path))
            {
                var all_tree_sp = GetTableFileList(txt_path);
                tree_table_select_list = all_tree_sp.Select(i => i.name).ToList();
            }
            else
            {
                tree_table_select_list = tree_table_select_list
                    .Where(i => i.LastIndexOf('.') > 0)
                    .Select(i => GetFileName(i))
                    .ToList();
            }

            foreach (var factory_item in tree_factory_select_list)
            {
                var dbpath = AppSettingConfig.dbconfigs.Where(i => i.db_name == factory_item).FirstOrDefault();
                var dataBaseVersionRep = new DataBaseVersionRep(dbpath.db_connection);
                var db = dataBaseVersionRep.IsCreate();
                if (!db)
                {
                    dataBaseVersionRep.CreateTable();
                    dataBaseVersionRep.Add(0);
                    Txt_Info($"{factory_item}，创建【databaseVersion】版本管理表成功", this.richTextBox2);
                }

                var versiondb = dataBaseVersionRep.Detail(0);
                if (versiondb == null)
                {
                    ischeck = false;
                    MessageBox.Show($"{factory_item}，数据库[databaseVersion]版本管理表无数据！");
                    Txt_Error($"{factory_item}，数据库[databaseVersion]版本管理表无数据！", this.richTextBox2);
                }
            }

            if (ischeck)
            {
                Txt_Info($"执行的数据库表文件数量：{tree_table_select_list.Count()}", this.richTextBox2);
                var dic_data = GetTableFileList(txt_path);
                foreach (var factory_item in tree_factory_select_list)
                {
                    Txt_Warn($"开始执行基地：{factory_item}", this.richTextBox2);
                    var dbpath = AppSettingConfig.dbconfigs.Where(i => i.db_name == factory_item).FirstOrDefault();
                    var dataBaseVersionRep = new DataBaseVersionRep(dbpath.db_connection);
                    var lastVersionData = dataBaseVersionRep.LastDetail();
                    int version = 0;
                    foreach (var dic_item in tree_table_select_list)
                    {
                        var data = dic_data.Where(i => i.name == dic_item).FirstOrDefault();
                        if (version < data.version)
                        {
                            version = data.version;
                        }
                        //Task.Run(() =>
                        //{
                        try
                        {
                            string[] strsql = File.ReadAllLines(data.str_path, Encoding.GetEncoding("GB2312"));
                            var sb = new StringBuilder();
                            foreach (var sqlitem in strsql)
                            {
                                if (sqlitem != "GO" && sqlitem != "go")
                                {
                                    sb.Append($"{sqlitem}\n");
                                }
                            }
                            var tableRep = new TableRep(dbpath.db_connection);
                            tableRep.ExecuteScalar(sb.ToString());
                            Txt_Info($"执行脚本：{data.name}", this.richTextBox2);
                        }
                        catch (Exception ex)
                        {
                            Txt_Error($"*****异常存数据库表名称：{data.name}，执行失败：{ex.Message}|{ex.StackTrace}|{ex.InnerException}", this.richTextBox2);
                        }
                        //});
                    }

                    //添加最后版本号
                    if (lastVersionData.Sp_Version < version)
                    {
                        //TUDO:版本号是否大于当前版本，大于才修改
                        dataBaseVersionRep.Add(version);
                    }
                    Txt_Info($"版本管理表最大版本号为：{version}", this.richTextBox2);
                    Txt_Warn($"结束执行程序", this.richTextBox2);
                }
            }

        }
        private void tree_table_AfterCheck(object sender, TreeViewEventArgs e)
        {
            var node = e.Node;
            if (node.Parent != null)
            {
                if (!node.Parent.Checked)
                {
                    node.Parent.Checked = true;
                }
            }
        }

        private void btn_table_clear_log_Click(object sender, EventArgs e)
        {
            this.richTextBox2.Text = "";
        }
        #endregion


        #region textsql

        /// <summary>
        /// 执行文本操作
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_text_sql_exec_Click(object sender, EventArgs e)
        {
            var tree_factory_select_list = new List<string>();
            var ischeck = true;
            string radioText = "";
            if (radioBtn_mobile_auth.Checked)
            {
                radioText = "mobileauth";
            }
            if (radioBtn_pc_auth.Checked)
            {
                radioText = "pcauth";
            }
            if (radioBtn_ems.Checked)
            {
                radioText = "ems";
            }
            if (string.IsNullOrEmpty(radioText))
            {
                ischeck = false;
                Txt_Error($"请选择要执行的数据库", this.richTextBox3);
            }
            if (string.IsNullOrEmpty(this.richText_SQL.Text.Trim()))
            {
                ischeck = false;
                Txt_Error($"请输入要执行的SQL脚本", this.richTextBox3);
            }

            tree_factory_select_list = GetAllCheckedNodesName(this.tree_factory).Split(',').Where(i => i != "").ToList();
            if (tree_factory_select_list.Count() <= 0)
            {
                ischeck = false;
                Txt_Error($"请选择要操作的基地信息！", this.richTextBox3);
            }

            //执行操作
            if (ischeck)
            {
                switch (radioText)
                {
                    case "ems":
                        ExecEMS(tree_factory_select_list);
                        break;
                    case "pcauth":
                        ExecPcAuth(tree_factory_select_list);
                        break;
                    case "mobileauth":
                        ExecMobileAuth(tree_factory_select_list);
                        break;
                }
            }
        }

        /// <summary>
        /// EMS
        /// </summary>
        /// <param name="tree_factory_select_list"></param>
        private void ExecEMS(List<string> tree_factory_select_list)
        {
            foreach (var factory_item in tree_factory_select_list)
            {
                var dbpath = AppSettingConfig.dbconfigs.Where(i => i.db_name == factory_item).FirstOrDefault();
                Txt_Warn($"开始执行基地：{factory_item}", this.richTextBox3);
                try
                {
                    var rep = new TableRep(dbpath.db_connection);
                    rep.ExecuteScalar(this.richText_SQL.Text);
                }
                catch (Exception ex)
                {
                    Txt_Error($"*****异常执行SQL失败：{ex.Message}|{ex.StackTrace}|{ex.InnerException}", this.richTextBox3);
                }
                Txt_Warn($"结束执行程序", this.richTextBox3);
            }
        }

        /// <summary>
        /// pc管理
        /// </summary>
        /// <param name="tree_factory_select_list"></param>
        private void ExecPcAuth(List<string> tree_factory_select_list)
        {
            foreach (var factory_item in tree_factory_select_list)
            {
                var dbpath = AppSettingConfig.dbconfigs.Where(i => i.db_name == factory_item).FirstOrDefault();
                Txt_Warn($"开始执行基地：{factory_item}", this.richTextBox3);
                try
                {
                    var rep = new TableRep(dbpath.db_pc_auth);
                    rep.ExecuteScalar(this.richText_SQL.Text);
                }
                catch (Exception ex)
                {
                    Txt_Error($"*****异常执行SQL失败：{ex.Message}|{ex.StackTrace}|{ex.InnerException}", this.richTextBox3);
                }
                Txt_Warn($"结束执行程序", this.richTextBox3);
            }
        }

        /// <summary>
        /// 移动端管理
        /// </summary>
        /// <param name="tree_factory_select_list"></param>
        private void ExecMobileAuth(List<string> tree_factory_select_list)
        {
            foreach (var factory_item in tree_factory_select_list)
            {
                var dbpath = AppSettingConfig.dbconfigs.Where(i => i.db_name == factory_item).FirstOrDefault();
                Txt_Warn($"开始执行基地：{factory_item}", this.richTextBox3);
                try
                {
                    var rep = new TableRep(dbpath.db_mobile_auth);
                    rep.ExecuteScalar(this.richText_SQL.Text);
                }
                catch (Exception ex)
                {
                    Txt_Error($"*****异常执行SQL失败：{ex.Message}|{ex.StackTrace}|{ex.InnerException}", this.richTextBox3);
                }
                Txt_Warn($"结束执行程序", this.richTextBox3);
            }
        }

        private void btn_textsql_clear_log_Click(object sender, EventArgs e)
        {
            this.richTextBox3.Text = "";
        }
        #endregion


    }
}

